package org.bbl.db.mybatis.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.session.RowBounds;
import org.bbl.db.domain.Bbl;

import java.util.List;

/**
 * @author biniamg
 * @since 1/3/13
 */
public interface BblMapper extends BaseMapper<Bbl> {


    @Select("SELECT * FROM ${param1}")
    List<Bbl> findByLanguage(@Param("param1") String param1);

    @Select("SELECT * FROM ${param1}_bible WHERE bookid = #{param2} AND chapterId = #{param3}")
    List<Bbl> findByKeyId(String param1, int param2, int param3);

    @Select("SELECT b.* FROM ${param1}_bible b, linking l WHERE b.bookId = l.desBook and b.chapterId = l.desChapter and b.startVerse &gt;= l.desStartVerse and b.startVerse &lt;= l.desEndVerse and l.srcBook =  #{param2} and l.srcChapter= #{param3} and l.srcVerse = #{param4}")
   List<Bbl> findLinking(String param1, int param2, int param3, int param4, RowBounds rowBounds);

    @Select("SELECT * FROM ${param1} WHERE " +
            "bookid &gt;= #{param2} AND  " +
            "bookid &lt;= #{param3} AND " +
            "(verse like #{param4} OR match (verse) against ( #{param5}) )")
    List<Bbl> findByLike(String param1, int param2, int param3, String param4, String param5, RowBounds rowBounds);

    @Select("SELECT m.* FROM ${param1} m, ${param2} v WHERE  v.verseId = m.verseId and " +
            "m.bookid &gt;= #{param3} AND v.bookid &gt;= #{param3} AND " +
            "m.bookid &lt;= #{param4} AND v.bookid &lt;= #{param4} AND " +
            "NOT (m.verse like #{param5} OR match (v.verse) against ( #{param6}) ) AND " +
            "(v.verse like #{param7} OR match (v.verse) against ( #{param8}) )")
    List<Bbl> findByApproximation(String param1, String param2,
                                  int param3, int param4,
                                  String param5, String param6,
                                  String param7, String param8,
                                  RowBounds rowBounds);

    @Insert("INSERT INTO ${param1} (verseId,bookId,chapterId,startVerse,endVerse,verse) VALUES" +
            "(#{param2.verseId}," +
            "#{param2.bookId}," +
            "#{param2.chapterId}," +
            "#{param2.startVerse}," +
            "#{param2.endVerse}," +
            "#{param2.verse})")
    void insertDynamicTable(String param1, @Param("param2") Bbl wordList);

}
